package com.youtills.resource;

import com.youtills.core.db.DataSourceType;
import com.youtills.core.db.YoutillsDataSource;
import com.youtills.core.lang.CollectionUtil;
import com.youtills.core.lang.InstanceMaker;
import com.youtills.core.lang.NumberUtil;
import com.youtills.core.lang.ObjectUtil;
import com.youtills.core.lang.StringUtil;
import com.youtills.action.ActionContext;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * TODO - !!!doc me!!!
 *
 * @author ravi.chiluveru
 * @since 23.0 (10/4/14)
 */
public class MySqlResourceStorage implements ResourceManager {

    private static final String COMMA = ", ";
    private static final String EQUALS = " = ";
    private static final String IN = " IN ";
    private static final String OPEN_BRACE = " ( ";
    private static final String CLOSE_BRACE = " ) ";

    private static final String SQL_SELECT = " SELECT ";
    private static final String SQL_FROM = " FROM ";
    private static final String SQL_WHERE = " WHERE ";
    private static final String SQL_AND = " AND ";


    private static final MySqlResourceStorage instance = new MySqlResourceStorage();
    private static final String REL_SELECT_QUERY =
            " SELECT " +
                    "     relationship_id " +
                    "   , left_resource_type " +
                    "   , left_resource_id " +
                    "   , right_resource_type " +
                    "   , right_resource_id " +
                    "   , order_value " +
                    " FROM res__resource_relationship " +
                    " WHERE (left_resource_type = ? AND left_resource_id = ?) " +
                    "       OR (right_resource_type = ? AND right_resource_id = ?) " +
                    " ORDER BY relationship_id, left_resource_id ";

    private static final String REL_LEFT_DEL_QUERY =
            " DELETE FROM res__resource_relationship " +
                    " WHERE relationship_id = ? AND left_resource_type = ? AND left_resource_id = ?";

    private static final String REL_RIGHT_DEL_QUERY =
            " DELETE FROM res__resource_relationship " +
                    " WHERE relationship_id = ? AND right_resource_type = ? AND right_resource_id = ?";

    private static final String REL_INSERT_QUERY =
            " INSERT INTO res__resource_relationship ( " +
                    "  relationship_id" +
                    ", left_resource_type" +
                    ", left_resource_id" +
                    ", right_resource_type" +
                    ", right_resource_id" +
                    ", order_value" +
                    ", created_date" +
                    //, modified_date
                    " ) VALUES (?, ?, ?, ?, ?, ?, ?);";

    private final YoutillsDataSource dataSource;

    private MySqlResourceStorage() {
        dataSource = YoutillsDataSource.getDataSource(DataSourceType.QGLDB);
    }

    public static MySqlResourceStorage getInstance() {
        return instance;
    }

    private static ResourceRelationship findRelationship(BaseResource resource, long relationshipId, ResourceRelationship.SourceLocation location) {
        for (ResourceRelationship rr : resource.getResourceRelationships()) {
            if (rr.getRelationship().getRelationshipId() == relationshipId && rr.getSourceLocation() == location) {
                return rr;
            }
        }
        return null;
    }

    private static String attributeName(ResourceAttributeValue attr) {

        Attribute attribute = attr.getAttribute();

        if (attribute instanceof MysqlAttribute) {
            return ((MysqlAttribute) attribute).getMysqlColumnName();
        }

        return attribute.getName();
    }

    private static void setParams(PreparedStatement statement, BaseResource resource) throws SQLException {
        if (!CollectionUtil.isEmpty(resource.getModifiedAttributes())) {
            for (int i = 0, j = 0; i < resource.getModifiedAttributes().size(); i++) {
                String attributeName = attributeName(resource.getModifiedAttributes().get(i));
                if (StringUtil.isEmpty(attributeName)) {
                    continue;
                }
                setParam(statement, ++j, resource.getModifiedAttributes().get(i));
            }
        }

    }

    private static String genInsertString(ActionContext ac, BaseResource resource) {

        resource.setCreatedDate(DateManager.instance.getCurrentDate(ac));

        StringBuilder insert = new StringBuilder("insert into ");
        StringBuilder paramList = new StringBuilder();
        insert.append(resource.getTableName());
        insert.append(" ( ");
        boolean needComma = false;
        if (!CollectionUtil.isEmpty(resource.getModifiedAttributes())) {
            for (ResourceAttributeValue attribute : resource.getModifiedAttributes()) {

                String attributeName = attributeName(attribute);
                if (StringUtil.isEmpty(attributeName)) {
                    continue;
                }

                if (needComma) {
                    insert.append(COMMA);
                    paramList.append(COMMA);
                } else {
                    needComma = true;
                }
                insert.append(attributeName);
                paramList.append(StringUtil.QUESTION);
            }
        }

        insert.append(" ) values (");
        insert.append(paramList.toString());
        insert.append(" );");

        return insert.toString();
    }

    private static String genUpdateString(BaseResource resource) {

        resource.setModifiedAt(new java.util.Date());

        StringBuilder update = new StringBuilder("update ");
        update.append(resource.getTableName());
        update.append(" set ");
        boolean needComma = false;
        if (!CollectionUtil.isEmpty(resource.getModifiedAttributes())) {
            for (ResourceAttributeValue attribute : resource.getModifiedAttributes()) {

                String attributeName = attributeName(attribute);
                if (StringUtil.isEmpty(attributeName)) {
                    continue;
                }

                if (needComma) {
                    update.append(COMMA);
                } else {
                    needComma = true;
                }
                update.append(attributeName);
                update.append(EQUALS);
                update.append(StringUtil.QUESTION);
            }
        }

        update.append(" where ");
        update.append(resource.getResourceIdColumn());
        update.append(EQUALS);
        update.append(resource.getResourceId());
        update.append(";");

        return update.toString();
    }

    private static String genMultiSelectString(final BaseResource resource, final Long[] resourceIds) {

        StringBuilder where = new StringBuilder();
        where.append(resource.getResourceIdColumn());
        where.append(IN);
        where.append(OPEN_BRACE);
        if (resourceIds != null) {
            boolean needComma = false;
            for (Long resourceId : resourceIds) {
                if (resourceId == null) {
                    continue;
                }
                if (!needComma) {
                    needComma = true;
                } else {
                    where.append(COMMA);
                }
                where.append(resourceId);
            }
        }

//        where.append(resourceIds);
        where.append(CLOSE_BRACE);
        return genSelectString(resource, where.toString());
    }

    private static String genSelectString(BaseResource resource, long resourceId) {

        StringBuilder where = new StringBuilder();
        where.append(resource.getResourceIdColumn());
        where.append(EQUALS);
        where.append(resourceId);

        return genSelectString(resource, where.toString());
    }

    private static String genSelectString(BaseResource resource, long parentResourceType, long parentResourceId) {

        StringBuilder where = new StringBuilder();
        where.append(resource.getParentResourceTypeColumn())
                .append(EQUALS)
                .append(parentResourceType)

                .append(SQL_AND)

                .append(resource.getParentResourceIdColumn())
                .append(EQUALS)
                .append(parentResourceId);

        return genSelectString(resource, where.toString());
    }

    private static String genSelectString(BaseResource resource, String where) {

        StringBuilder select = new StringBuilder(SQL_SELECT);
        select.append(resource.getResourceIdColumn());
        if (!CollectionUtil.isEmpty(resource.getAttributeValues())) {
            for (ResourceAttributeValue attribute : resource.getAttributeValues()) {
                String attributeName = attributeName(attribute);
                if (!StringUtil.isEmpty(attributeName)) {
                    select.append(COMMA);
                    select.append(attributeName);
                }
            }
        }

        select.append(SQL_FROM);
        select.append(resource.getTableName());

        if (!StringUtil.isEmpty(where)) {
            select.append(SQL_WHERE);
            select.append(where);
        }

        select.append(";");

        return select.toString();
    }

    private static void setParam(PreparedStatement statement, int index, ResourceAttributeValue attribute) throws SQLException {

        Object value = attribute.getValue();

        if (value instanceof Date) {
            statement.setDate(index, ((Date) value));
        } else if (value instanceof Integer) {
            statement.setInt(index, (Integer) value);
        } else if (value instanceof String) {
            statement.setString(index, (String) value);
        } else {
            statement.setObject(index, value);
        }
    }

    private <T extends Resource> BaseResource instance(Class<T> clazz) {
        T resource = InstanceMaker.newInstanceOf(clazz);
        if (resource instanceof BaseResource) {
            return (BaseResource) resource;
        }

        return null;
    }

    @Override
    public <T extends Resource> List<T> getResources(ActionContext ac, Class<T> clazz, ResourceFilter filter) {
        BaseResource resource = instance(clazz);
        String filterQuery = filter != null ? filter.getQueryFilter() : null;
        return getResources(ac, clazz, genSelectString(resource, filterQuery));
    }

    @Override
    public <T extends Resource> List<T> getResourcesBulk(ActionContext ac, Class<T> clazz, Long... resourceIds) {
        if (CollectionUtil.isEmpty(resourceIds)) {
            return null;
        }
        BaseResource resource = instance(clazz);
        return getResources(ac, clazz, genMultiSelectString(resource, resourceIds));
    }

    @Override
    public <T extends Resource> List<T> getLinkedResources(ActionContext ac, Resource resource, ResourceLink link) {
        return null;
    }

    @Override
    public <T extends Resource> T getResource(ActionContext ac, Class<T> clazz, long resourceId) {
        BaseResource resource = instance(clazz);
        List<T> resources = getResources(ac, clazz, genSelectString(resource, resourceId));
        if (resources.size() > 0) {
            return resources.get(0);
        }

        return null;
    }

    private <T extends Resource> List<T> getResources(ActionContext ac, Class<T> clazz, String querySql) {

        ArrayList<BaseResource> resources = new ArrayList<BaseResource>();

        try (
                Connection connection = dataSource.getConnection();
                PreparedStatement statement = connection.prepareStatement(querySql);
                ResultSet resultSet = statement.executeQuery()
        ) {
            while (resultSet.next()) {
                BaseResource resource = instance(clazz);
                resources.add(resource);
                resource.setResourceId(resultSet.getLong(resource.getResourceIdColumn()));

                for (ResourceAttributeValue attribute : resource.getAttributeValues()) {
                    final String attributeName = attributeName(attribute);
                    if (!StringUtil.isEmpty(attributeName)) {
                        if (attribute.getAttribute() instanceof Attribute.LongAttribute) {
                            attribute.setValue(resultSet.getLong(attributeName));
                        } else {
                            attribute.setValue(resultSet.getObject(attributeName));
                        }
                    }
                }
                getRelationships(resource, connection);
                resource.setDirty(false);
            }

        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        return (List<T>) resources;
    }

    private void getRelationships(BaseResource resource, Connection connection) throws SQLException {
        PreparedStatement statement = connection.prepareStatement(REL_SELECT_QUERY);
        statement.setLong(1, resource.getResourceType());
        statement.setLong(2, resource.getResourceId());
        statement.setLong(3, resource.getResourceType());
        statement.setLong(4, resource.getResourceId());

        ResultSet resultSet = statement.executeQuery();
        ResourceRelationship resourceRelationship = null;
        while (resultSet.next()) {
            long relationshipId = resultSet.getLong("relationship_id");
            final Long leftResourceType = NumberUtil.parseLong(resultSet.getObject("left_resource_type"));
            final Long leftResourceId = NumberUtil.parseLong(resultSet.getObject("left_resource_id"));
            final Long rightResourceType = NumberUtil.parseLong(resultSet.getObject("right_resource_type"));
            final Long rightResourceId = NumberUtil.parseLong(resultSet.getObject("right_resource_id"));
            final String orderValue = resultSet.getString("order_value");
            if (ObjectUtil.areEquals(leftResourceType, resource.getResourceType()) && ObjectUtil.areEquals(leftResourceId, resource.getResourceId())) {
                resourceRelationship = findRelationship(resource, relationshipId, ResourceRelationship.SourceLocation.left);
                if (resourceRelationship != null) {
                    resourceRelationship.addTargetResourceId(rightResourceType, rightResourceId, orderValue);
                }
            } else if (ObjectUtil.areEquals(rightResourceType, resource.getResourceType()) && ObjectUtil.areEquals(rightResourceId, resource.getResourceId())) {
                resourceRelationship = findRelationship(resource, relationshipId, ResourceRelationship.SourceLocation.right);
                if (resourceRelationship != null) {
                    resourceRelationship.addTargetResourceId(leftResourceType, leftResourceId, orderValue);
                }
            }
        }
    }

    private void set(BaseResource resource) {

    }

    @Override
    public <T extends ChildResource> List<T> getChildResources(ActionContext ac, Class<T> clazz, long parentResourceType, long parentResourceId) {
        BaseResource resource = instance(clazz);

        return (List<T>) getResources(ac, clazz, genSelectString(resource, parentResourceType, parentResourceId));
    }

    @Override
    public boolean saveResource(ActionContext ac, Resource resource) {

        if (resource != null && resource instanceof BaseResource && resource.isDirty()) {

            if (resource.getResourceId() != null) {
                updateResource(ac, (BaseResource) resource);
            } else {
                createResource(ac, (BaseResource) resource);
            }
            ((BaseResource) resource).setDirty(false);
        }
        return true;
    }

    private void updateResource(ActionContext ac, BaseResource resource) {

        try (
                Connection connection = dataSource.getConnection();
                PreparedStatement statement = connection.prepareStatement(genUpdateString(resource),
                        Statement.RETURN_GENERATED_KEYS)
        ) {
            setParams(statement, resource);
            int affectedRows = statement.executeUpdate();
            updateRelationships(ac, resource, connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private void createResource(ActionContext ac, BaseResource resource) {
        try (

                Connection connection = dataSource.getConnection();
                PreparedStatement statement = connection.prepareStatement(genInsertString(ac, resource),
                        Statement.RETURN_GENERATED_KEYS)
        ) {

            setParams(statement, resource);

            int affectedRows = statement.executeUpdate();
            if (affectedRows == 0) {
                throw new SQLException("Creating Resource failed, no rows affected.");
            }
            try (ResultSet generatedKeys = statement.getGeneratedKeys()) {
                if (generatedKeys.next()) {
                    resource.setResourceId(generatedKeys.getLong(1));
                } else {
                    throw new SQLException("Creating Resource failed, no ID obtained.");
                }
            }

            updateRelationships(ac, resource, connection);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private void updateRelationships(ActionContext ac, BaseResource resource, Connection connection) throws SQLException {
        List<ResourceRelationship> modifiedRelationships = resource.getModifiedRelationships();
        if (!CollectionUtil.isEmpty(modifiedRelationships)) {
            for (ResourceRelationship resourceRelationship : modifiedRelationships) {
                if (ResourceRelationship.SourceLocation.left == resourceRelationship.getSourceLocation()) {
                    PreparedStatement delStmt = connection.prepareStatement(REL_LEFT_DEL_QUERY);
                    delStmt.setLong(1, resourceRelationship.getRelationship().getRelationshipId());
                    delStmt.setLong(2, resource.getResourceType());
                    delStmt.setLong(3, resource.getResourceId());
                    delStmt.execute();
                    insertResourceRelationshipValues(ac, resource, resourceRelationship, connection);
                } else if (ResourceRelationship.SourceLocation.right == resourceRelationship.getSourceLocation()) {
                    PreparedStatement delStmt = connection.prepareStatement(REL_RIGHT_DEL_QUERY);
                    delStmt.setLong(1, resourceRelationship.getRelationship().getRelationshipId());
                    delStmt.setLong(2, resource.getResourceType());
                    delStmt.setLong(3, resource.getResourceId());
                    delStmt.execute();
                    insertResourceRelationshipValues(ac, resource, resourceRelationship, connection);
                }
            }
        }
    }

    private void insertResourceRelationshipValues(ActionContext ac, BaseResource resource, ResourceRelationship resourceRelationship, Connection connection) throws SQLException {
        PreparedStatement statement = connection.prepareStatement(REL_INSERT_QUERY);
        List<ResourceRelationshipValue> values = resourceRelationship.getValues();
        for (ResourceRelationshipValue value : values) {
            statement.setLong(1, resourceRelationship.getRelationship().getRelationshipId());
            if (resourceRelationship.getSourceLocation() == ResourceRelationship.SourceLocation.left) {
                statement.setLong(2, resource.getResourceType());
                statement.setLong(3, resource.getResourceId());
                statement.setObject(4, value.getResourceType());
                statement.setObject(5, value.getResourceId());
            } else if (resourceRelationship.getSourceLocation() == ResourceRelationship.SourceLocation.right) {
                statement.setObject(2, value.getResourceType());
                statement.setObject(3, value.getResourceId());
                statement.setLong(4, resource.getResourceType());
                statement.setLong(5, resource.getResourceId());
            } else {
                //may be throw exception??? - todo.
            }
            statement.setString(6, value.getOrderValue());
            statement.setDate(7, new Date(DateManager.instance.getCurrentTimeMillis(ac)));
            statement.addBatch();
        }
        statement.executeBatch();
    }

    @Override
    public <T extends Resource> T newResource(ActionContext ac, Class<T> clazz) {
        return InstanceMaker.newInstanceOf(clazz);
    }

    @Override
    public <T extends ChildResource> T newChildResource(ActionContext ac, Class<T> clazz, long parentResourceType, long parentResourceId) {
        T instance = InstanceMaker.newInstanceOf(clazz);

        if (instance instanceof BaseChildResource) {
            BaseChildResource resource = (BaseChildResource) instance;
            resource.setParentResourceType(parentResourceType);
            resource.setParentResourceId(parentResourceId);
        }

        return instance;
    }
}
